﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using HRM.DAL.DAO;
using HRM.DAL.Report;

namespace HRM.DAL.Gateway
{
    public class PunishmentGateway : DBGateway
    {
        public bool ApplyPunishment(Punishment punishment,int empId)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = string.Format("Insert into tblPunishment values(@EmpId,@ProcedureId,@Remarks,@TypeId,@date,@punishedByEmpId,@status,@releaseDate,@releaseBy)");
                CommandObj.Parameters.Clear();
                CommandObj.Parameters.Add("@EmpId", SqlDbType.Int).Value = empId;
                CommandObj.Parameters.Add("@ProcedureId", SqlDbType.Int).Value = punishment.Procedure.Id;
                CommandObj.Parameters.Add("@Remarks", SqlDbType.VarChar).Value = punishment.Remarks;
                CommandObj.Parameters.Add("@TypeId", SqlDbType.Int).Value = punishment.PunishdType.Id;
                CommandObj.Parameters.Add("@date", SqlDbType.DateTime).Value = punishment.Date;
                CommandObj.Parameters.Add("@punishedByEmpId", SqlDbType.VarChar).Value = punishment.PunishedBy;
                CommandObj.Parameters.Add("@status", SqlDbType.VarChar).Value = punishment.Status;
                CommandObj.Parameters.Add("@releaseDate", SqlDbType.DateTime).Value = punishment.ReleaseDate;
                CommandObj.Parameters.Add("@releaseBy", SqlDbType.VarChar).Value = punishment.ReleaseBy;
                CommandObj.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                throw new Exception("Error occured during Applying Punishment to Employee");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public bool IsAlreadyPunished(int empId)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = @"select * from tblPunishment where EmpId='" + empId + "' and status='ACTIVE'";
                SqlDataReader reader = CommandObj.ExecuteReader();
                while (reader.Read())
                {
                    return true;
                }
            }
            catch (Exception)
            {
                throw new Exception("Error in Data Base");
            }
            finally
            {
                ConnectionObj.Close();
            }
            return false;
        }

        public List<ViewAllPunisedmentEmployee> GetAllPunishedEmployee()
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = @"SELECT tblCode.CodeName, tblEmployees.Name, tblDesignations.Title, tblProjects.ProjectName, tblPunishmentType.Type, 
                                         tblPunishedProcedure.Punished,tblPunishment.Id,tblPunishment.Remarks, tblPunishment.status,tblPunishment.PunishedByEmpId,tblPunishment.date
                                         FROM  tblCode INNER JOIN tblEmployees ON tblCode.Id = tblEmployees.Code_CodeID INNER JOIN
                                         tblDesignations ON tblEmployees.DesignationID = tblDesignations.Id INNER JOIN
                                         tblProjects ON tblEmployees.ProjectID = tblProjects.ProjectID INNER JOIN
                                         tblPunishment ON tblEmployees.EmployeeId = tblPunishment.EmpId INNER JOIN
                                         tblPunishmentType ON tblPunishment.TypeId = tblPunishmentType.Id INNER JOIN
                                         tblPunishedProcedure ON tblPunishment.ProcedureId = tblPunishedProcedure.Id order by tblPunishment.date";
                SqlDataReader reader = CommandObj.ExecuteReader();
                List<ViewAllPunisedmentEmployee> viewAllPunisedmentEmployees=new List<ViewAllPunisedmentEmployee>();
                while (reader.Read())
                {
                    ViewAllPunisedmentEmployee punisedmentEmployee=new ViewAllPunisedmentEmployee();
                    punisedmentEmployee.EmpCode = reader[0].ToString();
                    punisedmentEmployee.Name = reader[1].ToString();
                    punisedmentEmployee.Designation = reader[2].ToString();
                    punisedmentEmployee.Project = reader[3].ToString();
                    punisedmentEmployee.Punishment = reader[4].ToString();
                    punisedmentEmployee.PunishedProcedure = reader[5].ToString();
                    punisedmentEmployee.Id = Convert.ToInt32(reader[6]);
                    punisedmentEmployee.Remarks = reader[7].ToString();
                    punisedmentEmployee.Status = reader[8].ToString();
                    punisedmentEmployee.PunishedBy = reader[9].ToString();
                    punisedmentEmployee.Date = Convert.ToDateTime(reader[10]);
                    viewAllPunisedmentEmployees.Add(punisedmentEmployee);
                }
                return viewAllPunisedmentEmployees;
            }
            catch (Exception)
            {
                throw new Exception();
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public List<ViewAllPunisedmentEmployee> GetAllPunishedEmployee(PunishmentType type)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = @"SELECT tblCode.CodeName, tblEmployees.Name, tblDesignations.Title, tblProjects.ProjectName, tblPunishmentType.Type, 
                                         tblPunishedProcedure.Punished, tblPunishment.Remarks,tblPunishment.Id,tblPunishment.status,tblPunishment.PunishedByEmpId,tblPunishment.date
                                         FROM  tblCode INNER JOIN tblEmployees ON tblCode.Id = tblEmployees.Code_CodeID INNER JOIN
                                         tblDesignations ON tblEmployees.DesignationID = tblDesignations.Id INNER JOIN
                                         tblProjects ON tblEmployees.ProjectID = tblProjects.ProjectID INNER JOIN
                                         tblPunishment ON tblEmployees.EmployeeId = tblPunishment.EmpId INNER JOIN
                                         tblPunishmentType ON tblPunishment.TypeId = tblPunishmentType.Id INNER JOIN
                                         tblPunishedProcedure ON tblPunishment.ProcedureId = tblPunishedProcedure.Id where tblPunishmentType.Type='" + type.Type + "' order by tblPunishment.date";
                SqlDataReader reader = CommandObj.ExecuteReader();
                List<ViewAllPunisedmentEmployee> viewAllPunisedmentEmployees = new List<ViewAllPunisedmentEmployee>();
                while (reader.Read())
                {
                    ViewAllPunisedmentEmployee punisedmentEmployee = new ViewAllPunisedmentEmployee();
                    punisedmentEmployee.EmpCode = reader[0].ToString();
                    punisedmentEmployee.Name = reader[1].ToString();
                    punisedmentEmployee.Designation = reader[2].ToString();
                    punisedmentEmployee.Project = reader[3].ToString();
                    punisedmentEmployee.Punishment = reader[4].ToString();
                    punisedmentEmployee.PunishedProcedure = reader[5].ToString();
                    punisedmentEmployee.Remarks = reader[6].ToString();
                    punisedmentEmployee.Id = Convert.ToInt32(reader[7]);
                    punisedmentEmployee.Status = reader[8].ToString();
                    punisedmentEmployee.PunishedBy = reader[9].ToString();
                    punisedmentEmployee.Date = Convert.ToDateTime(reader[10]);
                    viewAllPunisedmentEmployees.Add(punisedmentEmployee);
                }
                return viewAllPunisedmentEmployees;
            }
            catch (Exception)
            {
                throw new Exception();
            }
            finally
            {
                ConnectionObj.Close();
            }
        }
        public List<ViewAllPunisedmentEmployee> GetAllPunishedEmployee(Code code)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = @"SELECT tblCode.CodeName, tblEmployees.Name, tblDesignations.Title, tblProjects.ProjectName, tblPunishmentType.Type, 
                                         tblPunishedProcedure.Punished,tblPunishment.Id,tblPunishment.Remarks,tblPunishment.status,tblPunishment.PunishedByEmpId,tblPunishment.date
                                         FROM  tblCode INNER JOIN tblEmployees ON tblCode.Id = tblEmployees.Code_CodeID INNER JOIN
                                         tblDesignations ON tblEmployees.DesignationID = tblDesignations.Id INNER JOIN
                                         tblProjects ON tblEmployees.ProjectID = tblProjects.ProjectID INNER JOIN
                                         tblPunishment ON tblEmployees.EmployeeId = tblPunishment.EmpId INNER JOIN
                                         tblPunishmentType ON tblPunishment.TypeId = tblPunishmentType.Id INNER JOIN
                                         tblPunishedProcedure ON tblPunishment.ProcedureId = tblPunishedProcedure.Id where tblCode.CodeName='" + code.CodeName + "' order by tblPunishment.date";
                SqlDataReader reader = CommandObj.ExecuteReader();
                List<ViewAllPunisedmentEmployee> viewAllPunisedmentEmployees = new List<ViewAllPunisedmentEmployee>();
                while (reader.Read())
                {
                    ViewAllPunisedmentEmployee punisedmentEmployee = new ViewAllPunisedmentEmployee();
                    punisedmentEmployee.EmpCode = reader[0].ToString();
                    punisedmentEmployee.Name = reader[1].ToString();
                    punisedmentEmployee.Designation = reader[2].ToString();
                    punisedmentEmployee.Project = reader[3].ToString();
                    punisedmentEmployee.Punishment = reader[4].ToString();
                    punisedmentEmployee.PunishedProcedure = reader[5].ToString();
                    punisedmentEmployee.Id = Convert.ToInt32(reader[6]);
                    punisedmentEmployee.Remarks = reader[7].ToString();
                    punisedmentEmployee.Status = reader[8].ToString();
                    punisedmentEmployee.PunishedBy = reader[9].ToString();
                    punisedmentEmployee.Date = Convert.ToDateTime(reader[10]);
                    viewAllPunisedmentEmployees.Add(punisedmentEmployee);
                }
                return viewAllPunisedmentEmployees;
            }
            catch (Exception)
            {
                throw new Exception();
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public bool EditPunishedEmployee(Punishment punishment, int empId)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = string.Format("Update tblPunishment set EmpId=@EmpId,ProcedureId=@ProcedureId,Remarks=@Remarks,TypeId=@TypeId,date=@date,punishedByEmpId=@punishedByEmpId,status=@status where Id='"+punishment.Id+"'");
                CommandObj.Parameters.Clear();
                CommandObj.Parameters.Add("@EmpId", SqlDbType.Int).Value = empId;
                CommandObj.Parameters.Add("@ProcedureId", SqlDbType.Int).Value = punishment.Procedure.Id;
                CommandObj.Parameters.Add("@Remarks", SqlDbType.VarChar).Value = punishment.Remarks;
                CommandObj.Parameters.Add("@TypeId", SqlDbType.Int).Value = punishment.PunishdType.Id;
                CommandObj.Parameters.Add("@date", SqlDbType.DateTime).Value = punishment.Date;
                CommandObj.Parameters.Add("@punishedByEmpId", SqlDbType.VarChar).Value = punishment.PunishedBy;
                CommandObj.Parameters.Add("@status", SqlDbType.VarChar).Value = punishment.Status;
                CommandObj.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                throw new Exception("Error occured during Updating Punishment");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public bool ReleaseFromPunishment(Punishment punishment)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = string.Format("Update tblPunishment set Remarks=@Remarks,status=@status,releaseDate=@date,releaseBy=@releaseBy where Id='" + punishment.Id + "'");
                CommandObj.Parameters.Clear();
              
                CommandObj.Parameters.Add("@Remarks", SqlDbType.VarChar).Value = punishment.Remarks;
                CommandObj.Parameters.Add("@status", SqlDbType.VarChar).Value = punishment.Status;
                CommandObj.Parameters.Add("@date", SqlDbType.DateTime).Value = punishment.ReleaseDate;
                CommandObj.Parameters.Add("@releaseBy", SqlDbType.VarChar).Value = punishment.ReleaseBy;
               
                CommandObj.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                throw new Exception("Error occured during Updating Employee Punishment Status");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public string DeletePunish(int Id)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = @"Delete from tblPunishment where Id='" + Id + "'";
                CommandObj.ExecuteNonQuery();
                return "Punished Employee Deleted Successfully";
            }
            catch (Exception)
            {
                throw new Exception("Error occured during Deleting Punished Employee");
            }
            finally
            {
                ConnectionObj.Close();
            }
            
        }
    }
}